﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
namespace WPFExcelReport
{ 
    public class WGQITRACEData : BaseFunction
    {
        
        public WGQITRACEData()
            : base()
        {
           
        }
        public WGQITRACEData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }
        public DataSet GetRawData(DataRow dwABPOS)
        {
            DataSet set4Return = new DataSet();
            DataSet setLocal = new DataSet();
            DataSet setResult = new DataSet();
            DataSet setTemp = new DataSet();
            DataTable tableLocal = null;
            string VKPAUFNUM;
            string WBSRTDATE, WBENTDATE;
            WBSRTDATE = dwABPOS["WBSRTDATE"].ToString().Trim();
            WBENTDATE = dwABPOS["WBENTDATE"].ToString().Trim();
            try
            {
                OracleCommand or1 = OracleDB.CreateCommand();
                #region
                
                or1.CommandText = "SELECT DISTINCT A.WBEWDPART,TRIM(A.WBEWART)||'-'||TRIM(A.WBEWFARBE)||'-'||A.WBEWVAR AS ARTICLE, "
                    + " A.WBEWAUFKRS||'-'||TO_NUMBER(A.WBEWAUFLFD)||'-'||TO_NUMBER(A.WBEWAUFPOS) AS ORDERNO,'' AS REMARK "
                    + " FROM WBBEW A,WBBEW B,LAGPL C "
                    + " WHERE A.WBEWMANDANT = B.WBEWMANDANT AND A.WBEWJJJJ = B.WBEWJJJJ AND A.WBEWBELEG = B.WBEWBELEG "
                    + " AND A.WBEWLFDNR <> B.WBEWLFDNR AND A.WBEWMANDANT = C.LPMANDANT AND A.WBEWLO = C.LPLO "
                    + " AND A.WBEWLB = C.LPLB AND A.WBEWLP = C.LPPLATZ "                   
                    + " AND (C.LPLBKZ = 'L' OR C.LPLB = 'QI') AND A.WBEWWAKZ = 'F' "
                    + " AND A.WBEWBS IN ('13','27','41') AND TRIM (A.WBEWFC) = 'WB16' "
                    + " AND B.WBEWBS IN ('36','26','40') AND TRIM(B.WBEWFC) = 'WB16' "
                    + " AND A.WBEWBUCHDAT >= '" + WBSRTDATE + "' AND A.WBEWBUCHDAT <='" + WBENTDATE + "'"; 
                #endregion
                setLocal = Connect2Proxy(or1);
                if (setLocal.Tables.Count > 0)
                {
                    tableLocal = setLocal.Tables[0];
                    tableLocal.TableName = "WGQITRACE";
                    SetDefault(tableLocal);
                    string remark;
                    foreach (DataRow dwTemp in tableLocal.Rows)
                    {
                        GetREMARK(dwTemp);                       
                    }
                    
                    set4Return.Merge(setLocal);
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return set4Return;
        }
        public DataSet GetREMARK(DataRow dwABPOS)
        {
            DataSet set4Return = new DataSet();
            DataSet setLocal = new DataSet();
            DataTable tableLocal = null;
            try
            {
                OracleCommand or1 = OracleDB.CreateCommand();
                or1.CommandText = " SELECT DISTINCT A.WBBDPART,STRAGG(TRIM(TXTKTEXT))OVER(PARTITION BY A.WBBDPART ) AS REMARK  "
                    + " FROM WBBST A,WBFEHL B,TXTIND C,TXTKON D "
                    + " WHERE A.WBBMANDANT = B.WBFMANDANT AND A.WBBRWKRS = B.WBFRWKRS "
                    + " AND A.WBBRWLFD = B.WBFRWLFD AND A.WBBPOS = B.WBFRWPOS "
                    + " AND TRIM(B.WBFFSCHL) = 'TR' AND B.WBFMANDANT = C.TXTIMANDANT "
                    + " AND B.WBFRWKRS||B.WBFRWLFD||B.WBFRWPOS||B.WBFVERSION = RTRIM(C.TXTISIND) "
                    + " AND C.TXTIMANDANT = D.TXTKMANDANT AND C.TXTIPIND = D.TXTKPIND "
                    + " AND A.WBBDPART = :WBEWDPART "
                    ;

                or1.Parameters.Add("WBEWDPART", OracleType.NVarChar).Value = dwABPOS["WBEWDPART"].ToString();
                setLocal = Connect2Proxy(or1);

                if (setLocal.Tables.Count > 0)
                {
                    tableLocal = setLocal.Tables[0];

                    byte[] b;
                    string value;
                    string column = "";
                    string SUPPLIER = "";
                    foreach (DataRow dwTemp in tableLocal.Rows)
                    {
                        column = "REMARK";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[column].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[column] = value;
                        dwABPOS["REMARK"] = value;
                    }
                    
                }
                set4Return.Merge(setLocal);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex); ;
            }
            return set4Return;
        }
        public DataSet BuildWBBST(DataRow dwUI)
        {
            string progress = "查询完成";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                EventDispatcher.Instance.RaiseStatus("查询开始", StatusEventType.StartProgress);
                setResult = GetRawData(dwUI);
                setReturn.Merge(setResult);
                EventDispatcher.Instance.RaiseStatus("查询完成", StatusEventType.StopProgress);
            }
            catch (Exception ex)
            {
                Tracer.Error(ABPOS + ex.StackTrace, ex);
            }
            return setReturn;
        }
    }
}